package ProjectXXX;

import java.net.*;
import java.io.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.*;
import java.sql.*;
import module4.*;
import java.util.Vector;
import java.util.Date;
import java.text.SimpleDateFormat;

public class ReportsDAO
{
	
	private JTable reportTable;
	private Vector tempVect = null,rowData = null,colData = null;
	
	//dates
	private Date dateFrom = null;
	private Date dateTo = null;
	private SimpleDateFormat sdf = null;
	
	//Connection
	private Connection turtleConn;
	//*****************************************
	
	public ReportsDAO(Connection inConn)
	{	
		//connection to turtle Database

		sdf = new SimpleDateFormat("yyyy.MM.dd");
		turtleConn = inConn;
	}//end constructor
	
	
	public JTable topTenCust(Date dateFrom,Date dateTo)
	{
		
		try
		{
			Statement topTenStmt = turtleConn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet topTenCustRS;
			ResultSetMetaData rsmd;
			int amtMetaData;
			
			String topTenQuery = " SELECT surname,name,SUM(value) as Total " +
		                     " FROM customerTable,eventTable WHERE "+
		                     " eventTable.custid = customerTable.custid AND" +
		                     " eventDate BETWEEN '" + sdf.format(dateFrom) + "' AND '"+ sdf.format(dateTo)+"'" +
		                     " GROUP BY surname ORDER BY Total DESC";
		                     
		    System.out.println("QUERY :" + topTenQuery);        
		    topTenCustRS = topTenStmt.executeQuery(topTenQuery);   
		    rsmd = topTenCustRS.getMetaData();  
		    amtMetaData = rsmd.getColumnCount();
		    
		    colData = new Vector();
			rowData = new Vector();
			colData.addElement("#");
		    for(int i = 1;i <= amtMetaData;i++)
		    {
		    	colData.addElement(rsmd.getColumnName(i));
		    }//end for
		       
		    topTenCustRS.last();
			int amtOfRow = topTenCustRS.getRow();
			topTenCustRS.first();
			int orderer = 1;
			for(int i = 0;i < amtOfRow;i++)
			{	
			    tempVect = new Vector();
			    tempVect.addElement(orderer);
				tempVect.addElement(topTenCustRS.getString(1));
				tempVect.addElement(topTenCustRS.getString(2));
				tempVect.addElement(topTenCustRS.getDouble(3));
				rowData.addElement(tempVect);
				topTenCustRS.next();
				orderer++;
				if(orderer == 11)
				break;
			}//outerFor
			
			reportTable = new JTable(rowData,colData);  
		    reportTable.setEnabled(false);
		    reportTable.setFillsViewportHeight(true);
		    reportTable.setAutoCreateRowSorter(true);
		    reportTable.setGridColor(Color.black);
			//reportTable.setShowGrid(false);
		}//end try
		catch(SQLException sqlE)
		{
			sqlE.printStackTrace();
		}//end try catch
		return reportTable;
	}//end topTenCust
	
	
	public JTable topTenRentals(Date dateFrom,Date dateTo)
	{
		
		this.dateFrom = dateFrom;
		this.dateTo = dateTo;
		try
		{
			Statement topTenRentalStmt = turtleConn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet topTenRentalRS;
			ResultSetMetaData rsmd;
			int amtMetaData;
			
			String topRentalQuery = "SELECT stockTable.title,custEventTable.stockId," +
			                        "stockTypeTable.stockTypeName,stockTypeTable.mediaHardware," + 
			                        "COUNT(custEventTable.stockId) AS TimesRented" +
		                     " FROM eventTable,custEventTable,stockTable,stockTypeTable"+
		                     " WHERE (eventTable.eventId = custEventTable.eventId) AND " +
		                     "(custEventTable.stockId = stockTable.stockId) AND " + 
		                     "(eventTable.eventType = 'Loan') AND " +
		                     "(stockTable.stockTypeId = stockTypeTable.stockTypeId) AND" + 
		                     " eventDate BETWEEN '" + sdf.format(dateFrom) + "' AND '"+ sdf.format(dateTo)+"'" +
		                     " GROUP BY stockId ORDER BY TimesRented DESC";
		                     
		    System.out.println("QUERY :" + topRentalQuery);        
		    topTenRentalRS = topTenRentalStmt.executeQuery(topRentalQuery);   
		    rsmd = topTenRentalRS.getMetaData();  
		    amtMetaData = rsmd.getColumnCount();
		    
		    colData = new Vector();
			rowData = new Vector();
			colData.addElement("#");
		    for(int i = 1;i <= amtMetaData;i++)
		    {
		    	colData.addElement(rsmd.getColumnName(i));
		    }//end for
		       
		    topTenRentalRS.last();
			int amtOfRow = topTenRentalRS.getRow();
			topTenRentalRS.first();
			int orderer =1;
			for(int i = 0;i < amtOfRow;i++)
			{	
			    tempVect = new Vector();
			    tempVect.addElement(orderer);
				tempVect.addElement(topTenRentalRS.getString(1));
				tempVect.addElement(topTenRentalRS.getString(2));
				tempVect.addElement(topTenRentalRS.getString(3));
				tempVect.addElement(topTenRentalRS.getString(4));
				tempVect.addElement(topTenRentalRS.getString(5));
				rowData.addElement(tempVect);
				topTenRentalRS.next();
				orderer++;
				if(orderer == 11)
				break;
			}//outerFor
			
			reportTable = new JTable(rowData,colData);  
		    reportTable.setEnabled(false);
		    reportTable.setFillsViewportHeight(true);
		    reportTable.setAutoCreateRowSorter(true);
		    reportTable.setGridColor(Color.black);
		}//end try
		catch(SQLException sqlE)
		{
			sqlE.printStackTrace();
		}//end try catch
		return reportTable;
	}//end topTenRentals
	
	public JTable doSummary(Date dateFrom,Date dateTo)
	{
		
		this.dateFrom = dateFrom;
		this.dateTo = dateTo;
		try
		{
			Statement doSummarySTMT = turtleConn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet summaryRS;
			ResultSetMetaData rsmd;
			int amtMetaData;
			
			String summaryQuery = "SELECT stockTypeTable.stockTypeName,stockTypeTable.mediaHardware,"+
			                      "SUM(value) as Total "+
			                      "FROM eventTable,custEventTable,stockTypeTable,stockTable "+
			                      "WHERE (eventTable.eventId = custEventTable.eventId) AND " +
			                      "(custEventTable.stockId = stockTable.stockId) AND " +
			                      "(stockTable.stockTypeId = stockTypeTable.stockTypeId) AND " +
			                      "(eventTable.eventType = 'Loan') AND "+
		                    	  " eventDate BETWEEN '" + sdf.format(dateFrom) + "' AND '"+ sdf.format(dateTo)+"'" +
		                    	  " GROUP BY stockTypeName ORDER BY Total DESC";
		                     
		    System.out.println("QUERY :" + summaryQuery);        
		    summaryRS = doSummarySTMT.executeQuery(summaryQuery);   
		    rsmd = summaryRS.getMetaData();  
		    amtMetaData = rsmd.getColumnCount();
		    
		    colData = new Vector();
			rowData = new Vector();
			colData.addElement("#");
		    for(int i = 1;i <= amtMetaData;i++)
		    {
		    	colData.addElement(rsmd.getColumnName(i));
		    }//end for
		       
		    summaryRS.last();
			int amtOfRow = summaryRS.getRow();
			summaryRS.first();
			int orderer =1;
			for(int i = 0;i < amtOfRow;i++)
			{	
			    tempVect = new Vector();
			    tempVect.addElement(orderer);
				tempVect.addElement(summaryRS.getString(1));
				tempVect.addElement(summaryRS.getString(2));
				tempVect.addElement(summaryRS.getDouble(3));
				rowData.addElement(tempVect);
				summaryRS.next();
				orderer++;
			}//outerFor
			
			reportTable = new JTable(rowData,colData);  
		    reportTable.setEnabled(false);
		    reportTable.setFillsViewportHeight(true);
		    reportTable.setAutoCreateRowSorter(true);
		    reportTable.setGridColor(Color.black);
		}//end try
		catch(SQLException sqlE)
		{
			sqlE.printStackTrace();
		}//end try catch
		return reportTable;
	}//end doSummary
	
	public JTable getStockOut()
	{
		try
		{
			Statement outStockStmt = turtleConn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet outStockRS;
			ResultSetMetaData rsmd;
			int amtMetaData;
			
			String outStockQuery =  "SELECT stockId,stockTable.title,stockTypeName,dateLastRented AS DateRented," +
			                        "stockTypeTable.stockTypeName,stockTypeTable.mediaHardware,status" + 
		                     " FROM stockTable,stockTypeTable"+
		                     " WHERE (stockTable.stockTypeId = stockTypeTable.stockTypeId) AND status != 'IN'";
		                    
		                     
		    System.out.println("QUERY :" + outStockQuery);        
		    outStockRS = outStockStmt.executeQuery(outStockQuery);   
		    rsmd = outStockRS.getMetaData();  
		    amtMetaData = rsmd.getColumnCount();
		    
		    colData = new Vector();
			rowData = new Vector();
		    for(int i = 1;i <= amtMetaData;i++)
		    {
		    	colData.addElement(rsmd.getColumnName(i));
		    }//end for
		       
		    outStockRS.last();
			int amtOfRow = outStockRS.getRow();
			outStockRS.first();
			System.out.println("Before for loop");
			for(int i = 0;i < amtOfRow;i++)
			{	
			    tempVect = new Vector();
				tempVect.addElement(outStockRS.getString(1));
				tempVect.addElement(outStockRS.getString(2));
				tempVect.addElement(outStockRS.getString(3));
				tempVect.addElement(outStockRS.getString(4));
				tempVect.addElement(outStockRS.getString(5));
				tempVect.addElement(outStockRS.getString(6));
				tempVect.addElement(outStockRS.getString(7));
				rowData.addElement(tempVect);
				outStockRS.next();
			}//outerFor
			
			reportTable = new JTable(rowData,colData);  
		    reportTable.setEnabled(false);
		    reportTable.setFillsViewportHeight(true);
		    reportTable.setAutoCreateRowSorter(true);
		    reportTable.setGridColor(Color.black);
		}//end try
		catch(SQLException sqlE)
		{
			sqlE.printStackTrace();
		}//end try catch
		return reportTable;
	}
	
	public JTable getOldStock()
	{
		try
		{
			Statement oldStockStmt = turtleConn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet oldStockRS;
			ResultSetMetaData rsmd;
			int amtMetaData;
			Date curDate = new Date();
			String tempSTR = sdf.format(curDate);
			int yearINT = Integer.parseInt(tempSTR.substring(0,4));
			int monthINT = Integer.parseInt(tempSTR.substring(5,7));
			int dayINT = Integer.parseInt(tempSTR.substring(8));
			System.out.println("year :" +yearINT+" month: "+monthINT+" day : "+ dayINT );
			
			int newMonth = (monthINT - 6);
			if(newMonth < 0)
			{
				yearINT =- 1;
				newMonth = 12 - (newMonth);
			}
			else
			if(newMonth == 0)
			{
				newMonth = 12;
			}
			String comparedDate = new String(""+ yearINT +"-"+newMonth+"-"+dayINT);
			System.out.println(comparedDate);
			String oldStockQuery =  "SELECT title,stockTypeName,dateLastRented FROM stockTable,stockTypeTable WHERE (dateLastRented < '"+comparedDate+"') " +
			                        "AND (stockTable.stockTypeId = stockTypeTable.stockTypeId)";
			                 
		                    
		                     
		    System.out.println("QUERY :" + oldStockQuery);        
		    oldStockRS = oldStockStmt.executeQuery(oldStockQuery);   
		    rsmd = oldStockRS.getMetaData();  
		    amtMetaData = rsmd.getColumnCount();
		    
		    colData = new Vector();
			rowData = new Vector();
		    for(int i = 1;i <= amtMetaData;i++)
		    {
		    	colData.addElement(rsmd.getColumnName(i));
		    }//end for
		       
		    oldStockRS.last();
			int amtOfRow = oldStockRS.getRow();
			oldStockRS.first();
			System.out.println("Before for loop");
			for(int i = 0;i < amtOfRow;i++)
			{	
			    tempVect = new Vector();
				tempVect.addElement(oldStockRS.getString(1));
				tempVect.addElement(oldStockRS.getString(2));
				tempVect.addElement(oldStockRS.getString(3));
				System.out.println(oldStockRS.getString(3));
				rowData.addElement(tempVect);
				oldStockRS.next();
			}//outerFor
			
			reportTable = new JTable(rowData,colData);  
		    reportTable.setEnabled(false);
		    reportTable.setFillsViewportHeight(true);
		    reportTable.setAutoCreateRowSorter(true);
		    reportTable.setGridColor(Color.black);
		}//end try
		catch(SQLException sqlE)
		{
			sqlE.printStackTrace();
		}//end try catch
		return reportTable;
	}
	
	public JTable getEvents(String eventTypeIN,Date dateFrom,Date dateTo)
	{
		try
		{
			PreparedStatement eventsPStmt = turtleConn.prepareStatement("SELECT ");
			ResultSet eventsRS;
			ResultSetMetaData rsmd;
			int amtMetaData;
			String eventType = eventTypeIN;
			
			String eventsQuery =  "SELECT employeeTable.name,employeeTable.surname,eventTable.eventtype, " +
								  "	       eventTable.eventDate,"+
								  "	       custEventTable.eventId,eventTable.eventTime,"+
								  "	       custEventTable.stockId,stockTable.title"+
								  "	FROM   eventTable,custEventTable,employeeTable,stockTable"+
								  "	WHERE (custEventTable.stockId = stockTable.stockId) AND"+
								  "	      (custEventTable.eventId = eventTable.eventId) AND"+
								  "	      (eventTable.empId = employeeTable.empId) AND "+
								  "	      (eventTable.eventType = '"+eventType+"') AND" +
								  " (eventDate BETWEEN '" + sdf.format(dateFrom) + "' AND '"+ sdf.format(dateTo)+"')" ;
                   
		    System.out.println("QUERY :" + eventsQuery);        
		    eventsRS = eventsPStmt.executeQuery(eventsQuery);   
		    rsmd = eventsRS.getMetaData();  
		    amtMetaData = rsmd.getColumnCount();
		    
		    colData = new Vector();
			rowData = new Vector();
		    for(int i = 1;i <= amtMetaData;i++)
		    {
		    	colData.addElement(rsmd.getColumnName(i));
		    }//end for
		       
		    eventsRS.last();
			int amtOfRow = eventsRS.getRow();
			eventsRS.first();
			System.out.println("Before for loop");
			for(int i = 0;i < amtOfRow;i++)
			{	
			    tempVect = new Vector();
				tempVect.addElement(eventsRS.getString(1));
				tempVect.addElement(eventsRS.getString(2));
				tempVect.addElement(eventsRS.getString(3));
				tempVect.addElement(eventsRS.getString(4));
				tempVect.addElement(eventsRS.getString(5));
				tempVect.addElement(eventsRS.getString(6));
				tempVect.addElement(eventsRS.getString(7));
				tempVect.addElement(eventsRS.getString(8));
				rowData.addElement(tempVect);
				eventsRS.next();
			}//outerFor
			
			reportTable = new JTable(rowData,colData);  
		    reportTable.setEnabled(false);
		    reportTable.setFillsViewportHeight(true);
		    reportTable.setAutoCreateRowSorter(true);
		    reportTable.setGridColor(Color.black);
		}//end try
		catch(SQLException sqlE)
		{
			sqlE.printStackTrace();
		}//end try catch
		return reportTable;
	}
	
}//end class declaration

